package cc.linker.steplink.crm.merchant.util;


import cc.linker.steplink.crm.response.GlobalException;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.*;

public class ExcelDataUtil {

    /**
     * @param file 传入文件
     * @return
     */
    public static List<Map<String,String>> getAllByExcel(MultipartFile file){
        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
        List list = new ArrayList();
        try {
            String fileName = file.getOriginalFilename();// 文件原名称
            // 判断文件类型
            String type = null;// 文件类型
            type = fileName.indexOf(".") != -1 ? fileName.substring(fileName.lastIndexOf(".") + 1, fileName.length()) : null;


            InputStream ins = null;
            ins = file.getInputStream();
            File toFile = new File(file.getOriginalFilename());
            inputStreamToFile(ins, toFile);
            ins.close();
            //获取工作簿
            FileInputStream fs= new FileInputStream(toFile);
            if("xlsx".equalsIgnoreCase(type)){
                XSSFWorkbook workbook = new XSSFWorkbook(fs);
                XSSFSheet hs=workbook.getSheetAt(0);
                //获取Sheet的第一个行号和最后一个行号
                int last=hs.getLastRowNum();
                int first=hs.getFirstRowNum();
                //遍历获取单元格里的信息
                for (int i = first; i <=last; i++) {
                    Map<String,Object> map = new HashMap<>();
                    XSSFRow row=hs.getRow(i);
                    if(row!=null){
                        int firstCellNum=row.getFirstCellNum();//获取所在行的第一个行号
                        int lastCellNum=row.getLastCellNum();//获取所在行的最后一个行号
                        for (int j = firstCellNum; j <lastCellNum; j++) {
                            Object value = "";
                            if(!StringUtils.isEmpty(row.getCell(j))){
                                if(row.getCell(j).getCellType() == Cell.CELL_TYPE_NUMERIC){
                                    if(HSSFDateUtil.isCellDateFormatted(row.getCell(j))){
                                        Date dateCellValue = row.getCell(j).getDateCellValue();
                                        value = formatter.format(dateCellValue);
                                    }else{
                                        row.getCell(j).setCellType(Cell.CELL_TYPE_STRING);
                                        value=row.getCell(j).getStringCellValue();
                                    }
                                }else{
                                    row.getCell(j).setCellType(Cell.CELL_TYPE_STRING);
                                    value=row.getCell(j).getStringCellValue();
                                }
                            }
                            System.out.print(value+" ");
                            map.put(j+"",value);
                        }
                    }
                    list.add(map);
                    System.out.println();
                }
            }
            if("xls".equalsIgnoreCase(type)){
                HSSFWorkbook workbook=new HSSFWorkbook(fs);
                //获取第一个工作表
                HSSFSheet hs=workbook.getSheetAt(0);
                //获取Sheet的第一个行号和最后一个行号
                int last=hs.getLastRowNum();
                int first=hs.getFirstRowNum();
                //遍历获取单元格里的信息
                for (int i = first; i <=last; i++) {
                    Map<String,Object> map = new HashMap<>();
                    HSSFRow row=hs.getRow(i);
                    if(row!=null){
                        int firstCellNum=row.getFirstCellNum();//获取所在行的第一个行号
                        int lastCellNum=row.getLastCellNum();//获取所在行的最后一个行号
                        for (int j = firstCellNum; j <lastCellNum; j++) {
                            Object value = "";

                            if(!StringUtils.isEmpty(row.getCell(j))){
                                if(row.getCell(j).getCellType() == Cell.CELL_TYPE_NUMERIC){
                                    if(HSSFDateUtil.isCellDateFormatted(row.getCell(j))){
                                        Date dateCellValue = row.getCell(j).getDateCellValue();
                                        value = formatter.format(dateCellValue);
                                    }else{
                                        row.getCell(j).setCellType(Cell.CELL_TYPE_STRING);
                                        value=row.getCell(j).getStringCellValue();
                                    }
                                }else{
                                    row.getCell(j).setCellType(Cell.CELL_TYPE_STRING);
                                    value=row.getCell(j).getStringCellValue();
                                }
                            }
                            System.out.print(value+" ");
                            map.put(j+"",value);
                        }
                    }
                    list.add(map);
                    System.out.println();
                }
            }
            if (toFile.isFile()&& toFile.exists()) {
                toFile.delete();
            }
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            throw GlobalException.build("文件错误！");
        }

        return list;
    }



    //获取流文件
    private static void inputStreamToFile(InputStream ins, File file) {
        try {
            OutputStream os = new FileOutputStream(file);
            int bytesRead = 0;
            byte[] buffer = new byte[8192];
            while ((bytesRead = ins.read(buffer, 0, 8192)) != -1) {
                os.write(buffer, 0, bytesRead);
            }
            os.close();
            ins.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


}
